package com.huaxia.dao.excellent;

import com.huaxia.pojo.excellent.BranchFHS;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/08/12 17:00
 */

public interface BranchFHSMapper {

    @Select("select t.branchsname,t.branchPreNum,(t.branchNowNum-t.branchPreNum)branchIncreaseNum,t.branchNowNum,round(decode(t.branchPreNum,0,-9999,(t.branchNowNum-t.branchPreNum)*100/t.branchPreNum),1)branchYield   from(\n" +
            "select a.branchsname,NVL(b.branchPreNum,0)branchPreNum,NVL(c.branchNowNum,0)branchNowNum from SD_BRANCH a left join( \n" +
            "select SUBSTR(t.MANAGECOM,1,6)branchcode,count(SUBSTR(t.MANAGECOM,1,6)) branchPreNum from SD_FHAGENT4 t GROUP BY SUBSTR(t.MANAGECOM,1,6)\n" +
            ")b on a.branchcode=b.branchcode left join(\n" +
            "select SUBSTR(t.MANAGECOM,1,6)branchcode,count(SUBSTR(t.MANAGECOM,1,6)) branchNowNum from LAFHAGENT t where t.FHAGENTGRADE NOT IN('FH00','FH01','FH02','FH03') group by SUBSTR(t.MANAGECOM,1,6)\n" +
            ")c on a.branchcode = c.branchcode)t order by branchYield desc")
    List<BranchFHS> getBranchFHS();
}
